package com.szholly.pro.watergas_server.common.util;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.HashMap;

import com.szholly.pro.watergas_server.common.web.WebContext;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.htmlparser.Node;
import org.htmlparser.NodeFilter;
import org.htmlparser.Parser;
import org.htmlparser.filters.TagNameFilter;
import org.htmlparser.tags.TableColumn;
import org.htmlparser.util.NodeList;
import org.htmlparser.util.ParserException;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import lrq.common.MyLog;

import javax.servlet.http.HttpServletRequest;

public class ExcelCreater {
	
	Logger log = MyLog.getLogger(ExcelCreater.class.getName());
	
	private CellStyle cellStyle;
	
	private HashMap<String, CellStyle> cellStyleCache = new HashMap<>();
	
	public ExcelCreater(){
		
	}
	
	/**
	 * 导出excel
	 * @param tempPath freemarker模板根目录
	 * @param templateName freemarker模板文件名
	 * @param data freemarker模板数据
	 * @param out 输出流
	 * @throws IOException
	 * @throws ParserException
	 */
	public void exportExcel(String tempPath,String baseExcelField,String templateName,HashMap<String, Object> data,OutputStream out) throws IOException, ParserException{
		ByteArrayOutputStream htmlOut = new ByteArrayOutputStream();
		OutputStreamWriter htmlOutWriter = new OutputStreamWriter(htmlOut);
		exportHTML(tempPath, templateName, data, htmlOutWriter);
		ByteArrayInputStream htmlIn = new ByteArrayInputStream(htmlOut.toByteArray());
		HTML2Excel(htmlIn,out,baseExcelField);
		htmlOut.close();
	}
	
	/**
	 * 使用freemarker生成文件
	 * @param templateName
	 * @param tempPath
	 * @param data
	 * @param out
	 * @throws IOException 
	 */
	private void exportHTML(String tempPath,String templateName,HashMap<String, Object> data,Writer out) throws IOException{
		
		log.info("freeMarker基础目录："+tempPath+",模板名称："+templateName);
		
		Configuration cfg = new Configuration();
		cfg.setDirectoryForTemplateLoading(new File(tempPath));
		Template temp = cfg.getTemplate(templateName, "UTF-8");		
		
		try {
			temp.process(data, out);
		} catch (TemplateException e) {
			e.printStackTrace();
		}
		out.flush();
	}
	
	/**
	 * 把html转换成excel
	 * @param htmlIn html输入流
	 * @param out excel输出流
	 * @param baseExcelField 基excel文件，比如文件里面有公式之类的，数据默认填充到第一个工作簿,路径以freeMarkerTemp作为基础路径
	 * 			比如/WEB-INF/freeMarkerTemp/monitorData/tableTemp.xls,就写成/monitorData/tableTemp.xls
	 * @throws IOException
	 * @throws ParserException
	 */
	public void HTML2Excel(InputStream htmlIn,OutputStream out,String baseExcelField) throws IOException, ParserException{
		
		String htmlContext = inputStreamToString(htmlIn);
		Parser htmlParser = new Parser(htmlContext);
		NodeFilter trFilter = new TagNameFilter ("tr");
		NodeList trNodes = htmlParser.extractAllNodesThatMatch(trFilter);		
		HSSFWorkbook excelWorkBook = null;
		Sheet excelSheet =null; 
		
		if(baseExcelField == null){
			excelWorkBook = new HSSFWorkbook();
			excelSheet = excelWorkBook.createSheet(); 
		}
		else{
			HttpServletRequest resq = WebContext.getRequest();
			String tempPath =resq.getSession().getServletContext().getRealPath("/") + "WEB-INF/freeMarkerTemp/";
	        final InputStream in = new FileInputStream(tempPath+baseExcelField);
	        POIFSFileSystem modelbook = new POIFSFileSystem(in);  
	        excelWorkBook = new HSSFWorkbook(modelbook);
	        excelSheet = excelWorkBook.getSheetAt(0);
		}
		
		//第一列时间列宽度调整
		for(int rowIndex=0;rowIndex<trNodes.size();rowIndex++){
			
			Node trNode = trNodes.elementAt(rowIndex);
			Row excelRow = excelSheet.createRow(rowIndex);  
			NodeFilter tdFilter = new TagNameFilter ("td");
			NodeList tdNodes = new NodeList();
			trNode.collectInto(tdNodes, tdFilter);
			
			int colIndex = 0;
			
			for(int j=0;j<tdNodes.size();j++){
				Node tdNode = tdNodes.elementAt(j);
				if(tdNode == null || tdNode.getText().trim().equals("")){
					continue;
				}
				/**
				 * 如果当前列是属于合并单元格的话，计数器加一，表示跳到下一个单元格
				 */
				while(isMergedRegion(excelSheet, rowIndex, colIndex)){
					colIndex++;
				}
				
				TableColumn htmlTdTag = (TableColumn)tdNode;
				createCellFromTd(htmlTdTag, excelRow, rowIndex, colIndex);
				colIndex++;
			}
		}
		try {  
		    excelWorkBook.write(out);        
		} catch (IOException e) {  
			e.printStackTrace();
		} finally {  
		    try {  
		        out.close();  
		    } catch (IOException e) {  
		    	e.printStackTrace();
		    }  
		}  
	}
	
	/**
	 * 通过html的td创建excel单元格
	 * @param htmlTdTag 源html单元格
	 * @param excelRow 目标excel行
	 * @param rowIndex 要创建单元格的行位置
	 * @param colIndex 要创建单元格的列位置
	 * @return
	 */
	private Cell createCellFromTd(TableColumn htmlTdTag,Row excelRow,int rowIndex,int colIndex){
		String colspanStr = htmlTdTag.getAttribute("colspan");
		String rowspanStr = htmlTdTag.getAttribute("rowspan");
		
		int colspan = 1;
		int rowspan = 1;
		
		if(colspanStr != null){
			colspan = Integer.parseInt(colspanStr);
		}
		if(rowspanStr != null){
			rowspan = Integer.parseInt(rowspanStr);
		}
		
		if(colspan > 1 || rowspan > 1){
			excelRow.getSheet().addMergedRegion(new CellRangeAddress(rowIndex, rowspan+rowIndex-1, colIndex, colspan+colIndex-1));
			for(int i=0;i<rowspan;i++){
				Row mergedRow = excelRow.getSheet().createRow(rowIndex + i);
				for(int j=0;j<colspan;j++){
					Cell mergedCell = mergedRow.createCell(colIndex + j);
					mergedCell.setCellValue("");
					styleCellFromTd(htmlTdTag, mergedCell);
				}
			}
		}
		
		Cell cell = excelRow.createCell(colIndex);
		styleCellFromTd(htmlTdTag, cell);
		
		Object value = null;
		
		/**
		 * 如果是double类型的，就转成数字格式
		 */
		try {
			value = Double.valueOf(htmlTdTag.toPlainTextString());
			cell.setCellValue((Double)value);
		} catch (Exception e) {
			value = htmlTdTag.toPlainTextString();
			cell.setCellValue(htmlTdTag.toPlainTextString());
		}
		
		boolean isMergedRegion = isMergedRegion(cell.getSheet(), cell.getRowIndex(), cell.getColumnIndex());
		
		/**
		 * 如果是合并单元格，则不设置宽度
		 */
		if(!isMergedRegion){
			int preWidth = cell.getSheet().getColumnWidth(colIndex);
			
			if(value != null){
				int currentWidth = value.toString().getBytes().length * 256;
				if(currentWidth > preWidth){
					if(currentWidth>65280){
						cell.getSheet().setColumnWidth(colIndex, 100*256);
					}
					else{
						cell.getSheet().setColumnWidth(colIndex, currentWidth);
					}
				}
			}
		}
		
		return cell;
	}
	
	
	/**
	 * 通过html的td设置excel单元格的样式
	 * @param htmlTdTag
	 * @param cell
	 * @return
	 */
	private Cell styleCellFromTd(TableColumn htmlTdTag,Cell cell){
		
		String styleStr = htmlTdTag.getAttribute("style");
		
		if(styleStr == null || styleStr.equals("")){
			if(cellStyle == null){
				cellStyle = cell.getSheet().getWorkbook().createCellStyle();
				cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
				cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置单元格垂直方向对其方式
				cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
				cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
				cellStyle.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
				cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
				cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
				cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
				cellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
				cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色
				cellStyle.setWrapText(true);//自动换行
			}
			cell.setCellStyle(cellStyle);
			return cell;
		}
		
		if(cellStyleCache.containsKey(styleStr)){
			cell.setCellStyle(cellStyleCache.get(styleStr));
			return cell;
		}
		
		HashMap<String, String> styleInfoMap = new HashMap<>();
		String[] styleInfos = styleStr.split(";");
		for(String style : styleInfos){
			String[] styleItem = style.split(":");
			if(styleItem.length == 2){
				styleInfoMap.put(styleItem[0].toUpperCase().trim(), styleItem[1].toUpperCase().trim());
			}
		}
		
		CellStyle myCellStyle = cell.getSheet().getWorkbook().createCellStyle();
		myCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
		myCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置单元格垂直方向对其方式
		myCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
		myCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
		myCellStyle.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
		myCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
		myCellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
		myCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
		myCellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
		myCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色
		
		if(styleInfoMap.containsKey("TEXT-ALIGN")){
			String value = styleInfoMap.get("TEXT-ALIGN");
			if("CENTER".equals(value)){
				myCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  // 设置单元格水平方向对其方式
			}
			else if("LEFT".equals(value)){
				myCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);  // 设置单元格水平方向对其方式
			}
			else if("RIGHT".equals(value)){
				myCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);  // 设置单元格水平方向对其方式
			}
		}
		
		cellStyleCache.put(styleStr, myCellStyle);
		
		cell.setCellStyle(myCellStyle);
		return cell;
	}
	
	/**
	 * 检查单元格是否属于合并区
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private static boolean isMergedRegion(Sheet sheet,int row ,int column) {  
		  int sheetMergeCount = sheet.getNumMergedRegions();  
		  for (int i = 0; i < sheetMergeCount; i++) {  
			 CellRangeAddress range = sheet.getMergedRegion(i);  
			 int firstColumn = range.getFirstColumn();  
			 int lastColumn = range.getLastColumn();  
			 int firstRow = range.getFirstRow();  
			 int lastRow = range.getLastRow();  
			 if(row >= firstRow && row <= lastRow){  
				 if(column >= firstColumn && column <= lastColumn){  
					 return true;  
				 }  
			  }  
		  }  
		  return false;  
	} 
	
	/**
	 * 输入流转成字符串
	 * @param htmlIn
	 * @return
	 * @throws IOException
	 */
	private String inputStreamToString(InputStream htmlIn) throws IOException{
		StringBuilder result = new StringBuilder();
		BufferedReader reader = new BufferedReader(new InputStreamReader(htmlIn));
		for(String line=reader.readLine();line != null;line=reader.readLine()){
			result.append(line);
		}
		reader.close();
		return result.toString();
	}
}
